if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOIsForUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOIsForUser]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* Name:			GetCOIsForUser
* Purpose:		Retrieves all the COIs tied to the user
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* @admPersonId		Users AdmPerson id
******************************************************************/

CREATE Procedure [dbo].[GetCOIsForUser] (
		@admPersonId		INTEGER	
)
AS
BEGIN

	SET NOCOUNT ON

	Select	COI.Id,
		AdmPersonId,
		FwkDomainUserId,
		LastNotified,
		LastModified,
		DateSubmitted,
		DateReviewed,
		HasOutsideActivities,
		HasEquityInterests,
		HasFamilyAssociations,
		Royalty,
		OtherActivity,
		PolicyViolation,
		AggregateEquity,
		Status,
		RefCode.RefMeaning as StatusMeaning, 
		IsNull(DocumentId, 0) as DocumentId,
		CRP.Id as ReportingPeriodId,
		ReportingPeriod,
		IsNull(crm.NoteId , 0) as NoteId,
		COI.COITypeId
	FROM ConflictOfInterest COI (nolock)
		INNER JOIN RefCode ON COI.Status = RefCode.RefCode
		LEFT JOIN COIReportingPeriods CRP ON CRP.Id = COI.ReportingPeriodId
		LEFT JOIN Coi_ReviewNote_Map crm on crm.COIId = coi.Id
	WHERE AdmPersonId = @admPersonId
	ORDER BY COI.Id Desc
	
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  
GO
